package forum.dao.mysql;

import forum.dao.UserDAO;
import forum.dao.util.MySQLUtil;
import forum.entities.User;
import forum.exceptions.CreateExistingEntityException;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySQLUserDAO implements UserDAO {

    private static final String CREATE_QUERY =
            "INSERT INTO users(email,nickname,password) VALUES (NULL,NULL,NULL)";
    private static final String FIND_USER_BY_ID_QUERY =
            "SELECT * FROM users WHERE id = ?";
    private static final String UPDATE_QUERY =
            "UPDATE users SET email = ?, nickname = ?, password = ? WHERE id = ?";
    private static final String DELETE_QUERY =
            "DELETE FROM users WHERE id = ?";
    private static final String FIND_USER_BY_EMAIL_QUERY =
            "SELECT * FROM users WHERE email = ?";
    private static final String FIND_USER_BY_NICKNAME_QUERY =
            "SELECT * FROM users WHERE nickname = ?";
    private static ConnectionPool connectionPool = null;

    // TODO Migrate driver initialising and connection establishment to db in DAOFactory.
    static {
        String createTableQuery = "CREATE TABLE IF NOT EXISTS users" +
                "(id BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT, " +
                "email VARCHAR(50) UNIQUE, " +
                "nickname VARCHAR(50) UNIQUE, " +
                "password VARCHAR(50))";
        Connection conn = null;
        PreparedStatement createTableStatement = null;
        try {
            connectionPool = C3P0ConnectionPool.getInstance();
            conn =  connectionPool.getConnection();
            createTableStatement = conn.prepareStatement(createTableQuery);
            createTableStatement.execute();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(createTableStatement);
            MySQLUtil.close(conn);
        }
    }


    @Override
    public User create() {
        User user = null;
        PreparedStatement createUserStatement = null;
        ResultSet generatedKey = null;
        Connection connection = null;
        try {
            connection = connectionPool.getConnection();
            createUserStatement = MySQLUtil.prepareStatement(connection, CREATE_QUERY, true);
            createUserStatement.executeUpdate();
            generatedKey = createUserStatement.getGeneratedKeys();
            if (!generatedKey.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                // throw new DAOException("No generated key obtained");
            }
            user = new User(BigInteger.valueOf(generatedKey.getLong(1)));
            //user.setId(BigInteger.valueOf(generatedKey.getLong(1)));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLUtil.close(generatedKey);
            MySQLUtil.close(createUserStatement);
            MySQLUtil.close(connection);
        }
        return user;
    }

    @Override
    public User read(BigInteger id) {
        User user = null;
        Connection connection = null;
        PreparedStatement findUserByIdStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findUserByIdStatement = MySQLUtil.prepareStatement(connection, FIND_USER_BY_ID_QUERY, false, id);
            rs = findUserByIdStatement.executeQuery();
            if (!rs.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                // throw new DAOException("Empty ");
                return user;
            }
            user = new User(BigInteger.valueOf(rs.getLong("id")),
                                   rs.getString("email"),rs.getString("nickname"),rs.getString("password"));

        } catch (SQLException e) {
            // TODO auto-generated try-catch block
           // e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findUserByIdStatement);
            MySQLUtil.close(connection);
        }
        return user;
    }

    @Override
    public void update(User user) throws CreateExistingEntityException{
        if (user.getId() == null) {
            throw new IllegalArgumentException("Empty id field");
        }
        Object [] fields = {
                                   user.getEmail(),
                                   user.getNickName(),
                                   user.getPassword(),
                                   user.getId()
        };
        Connection connection = null;
        PreparedStatement updateUserStatement = null;
        try {
            connection = connectionPool.getConnection();
            updateUserStatement = MySQLUtil.prepareStatement(connection, UPDATE_QUERY, false, fields);
            updateUserStatement.executeUpdate();
        } catch (SQLException e) {
            throw new CreateExistingEntityException(e);
        } finally {
            MySQLUtil.close(updateUserStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public void delete(BigInteger id) {
        Connection connection = null;
        PreparedStatement deleteUserStatement = null;
        try {
            connection = connectionPool.getConnection();
            deleteUserStatement = MySQLUtil.prepareStatement(connection, DELETE_QUERY, false, id);
            deleteUserStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(deleteUserStatement);
            MySQLUtil.close(connection);
        }
    }

    // TODO Create one findBy method.

    @Override
    public User findUserByEmail(String email) {
        User user = null;
        Connection connection = null;
        PreparedStatement findUserByEmailStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findUserByEmailStatement = MySQLUtil.prepareStatement(connection, FIND_USER_BY_EMAIL_QUERY, false, email);
            rs = findUserByEmailStatement.executeQuery();
            if (!rs.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                return user;
            }
            user = new User(BigInteger.valueOf(rs.getLong("id")),
                    rs.getString("email"),rs.getString("nickname"),rs.getString("password"));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findUserByEmailStatement);
            MySQLUtil.close(connection);
        }
        return user;
    }

    @Override
    public User findUserByNickname(String nickname) {
        User user = null;
        Connection connection = null;
        PreparedStatement findUserByNicknameStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findUserByNicknameStatement = MySQLUtil.prepareStatement(connection,
                    FIND_USER_BY_NICKNAME_QUERY, false, nickname);
            rs = findUserByNicknameStatement.executeQuery();
            if (!rs.next()) {
                return user;
            }
            user = new User(BigInteger.valueOf(rs.getLong("id")),
                    rs.getString("email"),rs.getString("nickname"),rs.getString("password"));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
         //   e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findUserByNicknameStatement);
            MySQLUtil.close(connection);
        }
        return user;
    }
}
